
do $UNINSTALL_BODY$
declare
    l_object_name   text;
    l_object_type   text;
    l_err_message   text;
    l_len_name      int;
    l_len_type      int;
    l_len_message   int;
begin
    set client_min_messages='ERROR';

    -- 检查 compat_tools 是否存在
    select count(*)::int
      into l_len_type
      from pg_class as c
      join pg_namespace as n on c.relnamespace = n.oid
     where c.relname = 'compat_version'
       and n.nspname = 'compat_tools';

    if l_len_type = 0
    then
        set client_min_messages='NOTICE';
        raise notice ' >>> Compat tool object [compat_tools.compat_version] does not exists !!!';
        return;
    end if;

    -- 创建临时表，记录各个对象的删除情况
    CREATE temporary table if not exists temp_uninstall_result
    (
        object_type         text,
        object_name         text,
        uninstall_result    text
    ) on commit delete rows;

    -- 删除函数 (Aggregate)
    for l_object_name in select object_name
                           from compat_tools.compat_version
                          where compat_type = 'aggregate'
    loop
        begin
            execute 'drop aggregate '||l_object_name;
            insert into temp_uninstall_result values ('aggregate', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_uninstall_result values ('aggregate', l_object_name, 'Error: '||l_err_message);
        end;
    end loop;

    -- 删除函数 (function,procedure)
    for l_object_name in select object_name
                           from compat_tools.compat_version
                          where compat_type in ('function', 'procedure')
                            and object_name not in ('compat_tools.mg_sequence()', 'compat_tools.f_query_statment()')
    loop
        begin
            execute 'drop function "'||replace(replace(l_object_name, '.', '"."'), '(', '"(');
            insert into temp_uninstall_result values ('function', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                if l_err_message like '%builtin function can not be removed%'
                then
                    insert into temp_uninstall_result values ('function', l_object_name, 'Skip builtin function');
                elsif l_err_message like '%does not exist' and instr(l_object_name, '.') > 0
                then
                    begin
                        execute 'drop function "'||replace(substr(l_object_name, 1, instr(l_object_name, '(') - 1), '.', '"."')||'"';
                        insert into temp_uninstall_result values ('function', l_object_name, 'Removed');
                    exception
                        when others then
                            get stacked diagnostics l_err_message = message_text;
                            insert into temp_uninstall_result values ('function', l_object_name, 'Error: '||l_err_message);
                    end;
                else
                    insert into temp_uninstall_result values ('function', l_object_name, 'Error: '||l_err_message);
                end if;
        end;
    end loop;

    -- 删除视图
    for l_object_name in select viewname
                           from pg_views
                          where schemaname = 'compat_tools'
    loop
        begin
            execute 'drop view compat_tools."'||l_object_name||'" cascade';
            insert into temp_uninstall_result values ('view', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                if l_err_message like '%does not exist'
                then
                    insert into temp_uninstall_result values ('view', l_object_name, 'Removed');
                else
                    insert into temp_uninstall_result values ('view', l_object_name, 'Error: '||l_err_message);
                end if;
        end;
    end loop;

    -- 删除同义词
    for l_object_name in select '"'||n.nspname||'"."'||s.synname||'"'
                           from pg_synonym as s
                           join pg_namespace as n on n.oid = s.synnamespace
                          where s.synobjschema = 'compat_tools'
    loop
        begin
            execute 'drop synonym '||l_object_name;
            insert into temp_uninstall_result values ('synonym', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                if l_err_message like '%does not exist'
                then
                    insert into temp_uninstall_result values ('synonym', l_object_name, 'Removed');
                else
                    insert into temp_uninstall_result values ('synonym', l_object_name, 'Error: '||l_err_message);
                end if;
        end;
    end loop;

    -- 删除 Schema
    for l_object_name in select distinct substr(object_name, 1, instr(object_name, '.') - 1)
                           from compat_tools.compat_version
                          where instr(object_name, '.') > 1
                            and substr(object_name, 1, instr(object_name, '.') - 1) not in ('pg_catalog', 'compat_tools')
    loop
        begin
            execute 'drop schema "'||l_object_name||'"';
            insert into temp_uninstall_result values ('schema', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_uninstall_result values ('schema', l_object_name, 'Error: '||l_err_message);
        end;
    end loop;

    -- 其他特定对象
    for l_object_name in select 'drop table compat_tools.compat_version' union all
                         select 'drop table compat_tools.compat_testing' union all
                         select 'drop table compat_tools.pg_depend_list' union all
                         select 'drop table compat_tools.pg_function_list' union all
                         select 'drop function compat_tools.drop_compat_object' union all
                         select 'drop function compat_tools.drop_compat_package' union all
                         select 'drop function compat_tools.drop_compat_function' union all
                         select 'drop function compat_tools.f_unit_test' union all
                         select 'drop function compat_tools.mg_sequence' union all
                         select 'drop function compat_tools.f_query_statment' union all
                         select 'drop synonym public.dual' union all
                         select 'drop synonym pg_catalog.vsize'
    loop
        begin
            execute l_object_name;
            insert into temp_uninstall_result values ('SQL', l_object_name, 'Removed');
        exception
            when others then
                get stacked diagnostics l_err_message = message_text;
                insert into temp_uninstall_result values ('SQL', l_object_name, 'Error: '||l_err_message);
        end;
    end loop;

    -- 删除 compat_tools
    begin
        drop schema compat_tools;
        insert into temp_uninstall_result values ('schema', 'compat_tools', 'Removed');
    exception
        when others then
            get stacked diagnostics l_err_message = message_text;
            insert into temp_uninstall_result values ('schema', 'compat_tools', 'Error: '||l_err_message);
    end;

    -- 展示各个对象的删除情况
    select max(length(object_type)) + 1
         , max(length(object_name)) + 1
         , max(length(uninstall_result)) + 1
      into l_len_type, l_len_name, l_len_message
      from temp_uninstall_result;

    set client_min_messages='NOTICE';
    if l_len_name is null
    then
        raise notice ' >>> Uninstall result does not found !!!';
    else
        raise notice '%', '|-'||rpad('-', l_len_type, '-')||'---'||rpad('-', l_len_name, '-')||'---'||rpad('-', l_len_message, '-')||'-|';
        raise notice '%', '| '||rpad('TYPE', l_len_type)||' | '||rpad('NAME', l_len_name)||' | '||rpad('RESULT', l_len_message)||' |';
        raise notice '%', '|-'||rpad('-', l_len_type, '-')||'-+-'||rpad('-', l_len_name, '-')||'-+-'||rpad('-', l_len_message, '-')||'-|';
        for l_object_type, l_object_name, l_err_message in select object_type
                                                                , object_name
                                                                , uninstall_result
                                                             from temp_uninstall_result
                                                            order by uninstall_result, object_type, object_name
        loop
            raise notice '%', '| '||rpad(l_object_type, l_len_type)||' | '||rpad(l_object_name, l_len_name)||' | '||rpad(l_err_message, l_len_message)||' |';
        end loop;
        raise notice '%', '|-'||rpad('-', l_len_type, '-')||'---'||rpad('-', l_len_name, '-')||'---'||rpad('-', l_len_message, '-')||'-|';
    end if;
end;
$UNINSTALL_BODY$ language plpgsql;
